package com.dora.basic.mysql;

import java.sql.*;

/**
 * 各种 Statement区别
 *
 * @author hanchen
 * @date 2024/02/23
 */
public class KindsOfStatement {

    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "username";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            // 创建Statement对象
            Statement statement = conn.createStatement();

            // 使用Statement执行静态SQL语句
            ResultSet resultSet = statement.executeQuery("SELECT * FROM users");
            while (resultSet.next()) {
                System.out.println("User: " + resultSet.getString("username"));
            }

            // 创建PreparedStatement对象
            String sql="INSERT INTO users (username, password) VALUES (?, ?)";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, "newuser");
            preparedStatement.setString(2, "newpassword");

            // 使用PreparedStatement执行动态SQL语句
            int rowsAffected = preparedStatement.executeUpdate();
            System.out.println(rowsAffected + " row(s) affected");

            // 创建CallableStatement对象
            CallableStatement callableStatement = conn.prepareCall("{call my_stored_procedure(?, ?)}");
            callableStatement.setString(1, "input_parameter");
            callableStatement.registerOutParameter(2, Types.INTEGER);

            // 使用CallableStatement执行存储过程
            callableStatement.execute();
            int outputValue = callableStatement.getInt(2);
            System.out.println("Output parameter value: " + outputValue);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
